<?php
class DB{
    public $host;
    public $username;
    public $password;
    public $dbname;
    private $pdo;
 
    static $_instance = null;
    public static function getInstance(){
        if(null == self::$_instance){
            $className = get_called_class();
            self::$_instance = new self();
        }
        return self::$_instance;
    }
 
    public function __construct($cofigarr=array())
    {
		$this->host=$cofigarr['host'];
		$this->username=$cofigarr['username'];
		$this->password=$cofigarr['password'];
		$this->dbname=$cofigarr['dbname'];
        try{
            $dsn = "mysql:dbname={$this->dbname};host={$this->host}";
            $this->pdo = new Pdo($dsn, $this->username, $this->password);
            // 设置sql语句查询如果出现问题 就会抛出异常
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 设置sql语句查询如果出现问题 就会抛出异常
        } catch(PDOException $e){
            die("连接失败: ".$e->getMessage());
        }
    }
 
    /**
     * 查询一条记录
     * @param $table 表名
     * @param array $wheres 查询的条件
     * @param string $rows 查询的字段
     * @param string $group 分组条件
     * @param string $order 排序条件
     * @param string $limit 查询条数限制
     * @return mixed  查询的结果
     */
    public function query($table,$queryType="all",$wheres=[],$rows='*',$group='',$order='',$limit=''){
        try {
            //创建预处理sql语句
            $where = $wheres ? " WHERE " : '';
            $exec = [];
            foreach ($wheres as $k=>$v){
                $where .= "`{$k}` = :$k AND ";
                $exec[":{$k}"] = $v;
            }
            $where = $where ? rtrim($where,"AND ") : "";
            $sql = "SELECT {$rows} FROM `{$table}` {$where}";
            if($group!=''){
                $sql .= ' GROUP BY '.$group;
            }
            if($order!=''){
                $sql .= ' ORDER BY '.$order;
            }
            if($limit!=''){
                $sql .= ' LIMIT '.$limit;
            }
			//return $sql;
            //执行预处理sql语句，创建一个PDOStatement
            $pdoStmt = $this->pdo->prepare($sql);
            //判断$pdoStmt对象是否创建成功
            if ($pdoStmt != true) {
                return $this->pdo->errorInfo();//创建失败返回错误语句
            }
            //执行预处理语句：execute()
            $res = $pdoStmt->execute($wheres);
            $rows = [];
            if ($res == true && ($pdoStmt->rowCount() > 0)) {
                while ($row = $pdoStmt->fetch(PDO::FETCH_ASSOC)) {
                    if($queryType != 'all'){
                        return $row;
                    }
                    $rows[] = $row;
                }
            }
            return $rows;
        } catch (PDOException $e) {
            return 'Connect ERROR'.$e->getMessage();
        }
    }
	/**
	 * 查询多条记录
	 * @param $table 表名
	 * @param array $wheres 查询的条件
	 * @param string $rows 查询的字段
	 * @param string $group 分组条件
	 * @param string $order 排序条件
	 * @param string $limit 查询条数限制
	 * @return mixed  查询的结果
	 */
	public function querys($table,$queryType="all",$wheres=[],$rows='*',$group='',$order='',$limit=''){
	    try {
	        //创建预处理sql语句
	        $where = $wheres ? " WHERE " : '';
	        $exec = [];
	        foreach ($wheres as $k=>$v){
	            $where .= "`{$k}` = :$k AND ";
	            $exec[":{$k}"] = $v;
	        }
	        $where = $where ? rtrim($where,"AND ") : "";
	        $sql = "SELECT {$rows} FROM `{$table}` {$where}";
	        if($group!=''){
	            $sql .= ' GROUP BY '.$group;
	        }
	        if($order!=''){
	            $sql .= ' ORDER BY '.$order;
	        }
	        if($limit!=''){
	            $sql .= ' LIMIT '.$limit;
	        }
			//return $sql;
	        //执行预处理sql语句，创建一个PDOStatement
	        $pdoStmt = $this->pdo->prepare($sql);
	        //判断$pdoStmt对象是否创建成功
	        if ($pdoStmt != true) {
	            return $this->pdo->errorInfo();//创建失败返回错误语句
	        }
	        //执行预处理语句：execute()
	        $res = $pdoStmt->execute($wheres);
	        $rows = [];
	        if ($res == true && ($pdoStmt->rowCount() > 0)) {
	            while ($row = $pdoStmt->fetchAll(PDO::FETCH_ASSOC)) {
	                if($queryType != 'all'){
	                    return $row;
	                }
	                $rows[] = $row;
	            }
	        }
	        return $rows;
	    } catch (PDOException $e) {
	        return 'Connect ERROR'.$e->getMessage();
	    }
	}
    /**
     * 插入数据
     * @param $table 表名
     * @param $data 要插入的数据,eg:array("字段名"=>值)
     * @return string 返回刚插入的ID
     */
    public function insert($table,$data){
        try{
            $filed = '';
            $value = '';
            $exec = [];
            //根据要插入的数据，组装预处理的sql语句
            foreach ($data as $k => $v){
                $filed.= "`{$k}`,";
                $value.= "?,";
                $exec[] = $v;
            }
            $filed = rtrim($filed,",");
            $value = rtrim($value,",");
            $sql = "insert into `{$table}` ({$filed}) values({$value})";
			//return $sql;
            //执行预处理sql语句，创建一个PDOStatement
            $stmt = $this->pdo->prepare($sql);
            //判断$pdoStmt对象是否创建成功
            if ($stmt != true) {
                return $this->pdo->errorInfo();//创建失败返回错误语句
            }
            $stmt->execute($exec);//执行预处理语句：execute()
            return $this->pdo->lastInsertId();
        }catch (PDOException $e) {
            return 'Connect ERROR'.$e->getMessage();
        }
    }
 
    /**
     * 更新数据库的数据
     * @param $table 表名
     * @param $data 要更新的数据
     * @param array $wheres 更新数据的条件
     * @return array|int|string 返回更新的条数
     */
    public function update($table,$data,$wheres=[]){
        try{
            //创建预处理sql语句
            $set = "";
            $where = $wheres ? " WHERE " : '';
            $exec=[];
            foreach ($data as $k => $v){
                $set .= "`{$k}` =:{$k},";
                $exec[":{$k}"] = $v;
            }
            $set = rtrim($set,",");
            foreach ($wheres as $key => $value){
                $where .= "`{$key}` = :$key AND ";
                $exec[":{$key}"] = $value;
            }
            $where = $where ? rtrim($where,"AND ") : "";
            $sql="UPDATE `{$table}` SET {$set} {$where}";
            //执行预处理sql语句，创建一个PDOStatement
            $stmt = $this->pdo->prepare($sql);
            //判断$pdoStmt对象是否创建成功
            if ($stmt != true) {
                return $this->pdo->errorInfo();//创建失败返回错误语句
            }
            if($stmt->execute($exec)){
                //成功会返回受影响的记录数
                return $stmt->rowCount();
            }else{
                return $stmt->errorInfo();
            }
        } catch (PDOException $e){
            return 'Connect ERROR'.$e->getMessage();
        }
    }
 
    /**
     * 删除数据
     * @param $table 表名
     * @param array $wheres 删除的条件
     * @return array|int|string 返回受影响的记录数
     */
    public function  del($table,$wheres=[]){
        try{
            //创建预处理sql语句
            $where = $wheres ? " WHERE " : '';
            $exec = [];
            foreach ($wheres as $k=>$v){
                $where .= "`{$k}` = :$k AND ";
                $exec[":{$k}"] = $v;
            }
            $where = $where ? rtrim($where,"AND ") : "";
            $sql = "DELETE FROM `{$table}` {$where}";
			//return $sql;
            //执行预处理sql语句，创建一个PDOStatement
            $stmt = $this->pdo->prepare($sql);
            //判断$pdoStmt对象是否创建成功
            if ($stmt != true) {
                return $this->pdo->errorInfo();//创建失败返回错误语句
            }
            if($stmt->execute($exec)){
                //成功会返回受影响的记录数
                return $stmt->rowCount();
            }else{
                return $stmt->errorInfo();
            }
        } catch (PDOException $e){
            return 'Connect ERROR'.$e->getMessage();
        }
    }
 
    /**
     * 执行原装的SQL语句查询
     * @param $sql sql语句
     * @param $exec 预处理的条件
     * @return array|string
     */
    public function execSql($sql,$exec){
        try {
            //执行预处理sql语句，创建一个PDOStatement
            $pdoStmt = $this->pdo->prepare($sql);
            //判断$pdoStmt对象是否创建成功
            if ($pdoStmt != true) {
                return $this->pdo->errorInfo();//创建失败返回错误语句
            }
            //执行预处理语句：execute()
            $res = $pdoStmt->execute($exec);
            $rows = [];
            if ($res == true && ($pdoStmt->rowCount() > 0)) {
                while ($row = $pdoStmt->fetch(PDO::FETCH_ASSOC)) {
                    $rows[] = $row;
                }
            }
            return $rows;
        } catch (PDOException $e){
            return "ERROR:".$e->getMessage();
        }
    }
}
?>